package com.young4j.xinsuiwutuobang.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.Properties;





import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


public class DataBaseUtils {
	private static final Logger LOGGER = LoggerFactory.getLogger(DataBaseUtils.class);
	//org.apache.commons.dbutils.QueryRunner
	private static final QueryRunner QUERY_RUNNER;
	//java.lang.ThreadLocal<Connection>
	private static final ThreadLocal<Connection> CONNECTION_HOLDER;
	//java.lang.ThreadLocal<Connection>
	private static final BasicDataSource DATA_SOURCE;
	static{
		QUERY_RUNNER = new QueryRunner();
		CONNECTION_HOLDER = new ThreadLocal<Connection>();
		DATA_SOURCE = new BasicDataSource();
		String driver;
		String url;
		String username;
		String password;
		Properties conf = PropsUtils.loadProps("conf.properties");
		driver = conf.getProperty("jdbc.driver");
		url = conf.getProperty("jdbc.url");
		username = conf.getProperty("jdbc.username");
		password = conf.getProperty("jdbc.password");
		DATA_SOURCE.setDriverClassName(driver);
		DATA_SOURCE.setUrl(url);
		DATA_SOURCE.setUsername(username);
		DATA_SOURCE.setPassword(password);
		
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			LOGGER.error("can not load jdbc driver",e);
		}
	}
	/**
	 * 获取连接
	 * 将Connection放入ThreadLocal的未知泛型中
	 * 通过get方法来获取Connection实例，如果获取结果为空则设置一个Connection
	 * @return
	 */
	public static Connection getConnection(){
		Connection conn = CONNECTION_HOLDER.get();
		if(conn == null){
			try {
				conn = DATA_SOURCE.getConnection();
			} catch (SQLException e) {
				LOGGER.error("get connection failure",e);
				throw new RuntimeException(e);
			} finally {
				CONNECTION_HOLDER.set(conn);
			}
		}
		
		return conn;
	}
	//关闭连接-池化之后删点该方法
	/*public static void closeConnection(Connection conn){
		if(conn != null){
			try {
				conn.close();
			} catch (SQLException e) {
				LOGGER.error("close connection failure",e);
			}
		}
	}*/
	/**
	 * 集合查询
	 * @param entityClass
	 * @param sql
	 * @param params
	 * @return
	 */
	public static <T> List<T> queryEntityList(Class<T> entityClass,String sql,Object...params){
		List<T> entityList;
		Connection conn = getConnection();
		try {
			entityList = QUERY_RUNNER.query(conn, sql, new BeanListHandler<T>(entityClass), params);
		} catch (SQLException e) {
			LOGGER.error("query entity list failure",e);
			throw new RuntimeException(e);
		} 
		return entityList;
	}
	/**
	 * 单个实体查询
	 * @param entityClass
	 * @param sql
	 * @param params
	 * @return
	 */
	public static <T> T queryEntity(Class<T> entityClass,String sql,Object...params){
		T entity;
		Connection conn = getConnection();
		try {
			entity = QUERY_RUNNER.query(conn, sql,new BeanHandler<T>(entityClass),params);
		} catch (SQLException e) {
			LOGGER.error("query entity failure",e);
			throw new RuntimeException(e);
		}
		return entity;
	}
	/**
	 * 多表关联查询
	 * -待衍生--
	 * 
	 * 
	 * 
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public static List<Map<String,Object>> executeQuery(String sql,Object...params){
		List<Map<String,Object>> result = null;
		Connection conn = getConnection();
		try {
			result = QUERY_RUNNER.query(conn, sql, new MapListHandler(), params);
		} catch (SQLException e) {
			LOGGER.error("execute query failure",e);
			throw new RuntimeException(e);
		}
		return result;
	}
	/**
	 * 更新操作
	 * @param sql
	 * @param params
	 * @return
	 */
	public static int executeUpdate(String sql,Object...params){
		int rows = 0;
		Connection conn = getConnection();
		try {
			rows = QUERY_RUNNER.update(conn, sql, params);
		} catch (SQLException e) {
			LOGGER.error("execute update failure",e);
			throw new RuntimeException(e);
		}
		return rows;
	}
	/**
	 * 插入操作
	 * 将插入的字段放入map中
	 * 循环map的key拼装columns和values，这里不要使用判断来操作‘，’
	 * 直接在循环后使用replace替换掉最后的‘，’为最佳，
	 * 通过map.values().toArray()生成参数
	 * @param entityClass
	 * @param fieldMap
	 * @return
	 */
	public static <T> boolean insertEntity(Class<T> entityClass,Map<String, Object> fieldMap){
		if(CollectionUtils.isEmpty((Collection<?>)fieldMap)){
			LOGGER.error("can not insert entity:fieldMap is empty");
			return false;
		}
		String sql = "INSERT INTO " + getTableName(entityClass);
		StringBuilder columns = new StringBuilder("(");
		StringBuilder values = new StringBuilder("(");
		for(String fieldKey:fieldMap.keySet()){
			columns.append(fieldKey).append(",");
			values.append("?,");
		}
		columns.replace(columns.lastIndexOf(","), columns.length(), ")");
		values.replace(values.lastIndexOf("?,"), values.length(), ")");
		sql += columns + "VALUES " + values;
		Object[] params = fieldMap.values().toArray();
		return executeUpdate(sql, params) == 1;
	}
	/**
	 * 更新实体
	 * 根据id更新实体，将要更新的字段放入map中，更新操作传入泛型实体仅为了获取实体类名
	 * @param entityClass
	 * @param id
	 * @param fieldMap
	 * @return
	 */
	public static<T> boolean updateEntity(Class<T> entityClass,long id,Map<String,Object> fieldMap){
		if(CollectionUtils.isEmpty(fieldMap)){
			LOGGER.error("can not update entity: fieldMap is empty");
			return false;
		}
		String sql = "UPDATE " + getTableName(entityClass) + " SET ";
		StringBuilder columns = new StringBuilder();
		for(String fieldKey:fieldMap.keySet()){
			columns.append(fieldKey).append("=?,");
		}
		sql += columns.substring(0,columns.lastIndexOf("=?,")) + "WHERE id=?";
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(id);
		Object [] params = paramList.toArray();
		return executeUpdate(sql, params) == 1;
	}
	/**
	 * 删除实体
	 * 根据id删除实体
	 * @param entityClass
	 * @param id
	 * @return
	 */
	public static <T> boolean deleteEntity(Class<T> entityClass,long id){
		String sql = "DELETE FROM " + getTableName(entityClass) + " WHERER id=?";
		return executeUpdate(sql, id) == 1;
	}
	/**
	 * 获取类名
	 * 用于操作数据库是获得表名，这里实体类名要与数据库表名一致，不然操作失败
	 * @param entityClass
	 * @return
	 */
	public static String getTableName(Class<?> entityClass){
		return entityClass.getSimpleName();
	}
}
